// Excel导入服务
const XLSX = require('xlsx');
const path = require('path');
const fs = require('fs');


// 时间标准化辅助函数（支持 9:5/中文点分/全角冒号/纯数字/Excel小数 → HH:mm）
function standardizeTimeHelper(timeInput) {
  try {
    if (timeInput === undefined || timeInput === null) return null;
    let s = String(timeInput).trim();
    if (!s) return null;

    if (!isNaN(s) && s.includes('.')) {
      const dayFrac = parseFloat(s);
      if (!isNaN(dayFrac) && dayFrac >= 0 && dayFrac < 1) {
        const totalMinutes = Math.round(dayFrac * 24 * 60);
        const hh = String(Math.floor(totalMinutes / 60)).padStart(2, '0');
        const mm = String(totalMinutes % 60).padStart(2, '0');
        return `${hh}:${mm}`;
      }
    }

    s = s.replace(/：/g, ':').replace(/[点时]/g, ':').replace(/[分]/g, '');

    if (/^\d{3,4}$/.test(s)) {
      const num = s.padStart(4, '0');
      return `${num.slice(0, 2)}:${num.slice(2, 4)}`;
    }

    if (/^\d{1,2}:\d{1,2}(:\d{1,2})?$/.test(s)) {
      const parts = s.split(':');
      const hh = String(parseInt(parts[0] || '0', 10)).padStart(2, '0');
      const mm = String(parseInt(parts[1] || '0', 10)).padStart(2, '0');
      return `${hh}:${mm}`;
    }

    return s;
  } catch (e) {
    console.warn('时间标准化异常:', e.message);
    return null;
  }
}

class ExcelImportService {
  constructor(db, dbPool = null) {
    this.db = db;
    this.dbPool = dbPool;
  }

  /**
   * 获取数据库连接（优先使用连接池）
   */
  async getDbConnection() {
    if (this.dbPool) {
      try {
        return await this.dbPool.getConnection();
      } catch (error) {
        console.warn('连接池获取连接失败，使用备用连接:', error.message);
        return this.db;
      }
    }
    return this.db;
  }

  /**
   * 释放数据库连接
   */
  async releaseDbConnection(connection) {
    if (this.dbPool && connection !== this.db) {
      try {
        await this.dbPool.releaseConnection(connection);
      } catch (error) {
        console.warn('释放连接失败:', error.message);
      }
    }
  }

  /**
   * 导入PPH目标值
   * @param {string} filePath Excel文件路径
   * @returns {Promise<Object>} 导入结果
   */
  async importPPHTargets(filePath) {
    try {
      // 读取Excel文件
      const workbook = XLSX.readFile(filePath);
      const sheetName = workbook.SheetNames[0];
      const worksheet = workbook.Sheets[sheetName];

      // 转换为JSON数据
      const jsonData = XLSX.utils.sheet_to_json(worksheet, { defval: '', blankrows: false, raw: false, rawNumbers: true });

      if (jsonData.length === 0) {
        return { success: false, message: 'Excel文件为空' };
      }

      // 验证数据格式
      const validationResult = this.validateTargetData(jsonData);
      if (!validationResult.success) {
        return validationResult;
      }

      // 执行导入
      const importResult = await this.insertTargetData(jsonData);

      // 清理临时文件
      if (fs.existsSync(filePath)) {
        fs.unlinkSync(filePath);
      }

      return importResult;

    } catch (error) {
      console.error('导入PPH目标值失败:', error);

      // 清理临时文件
      if (fs.existsSync(filePath)) {
        fs.unlinkSync(filePath);
      }

      return {
        success: false,
        message: '导入失败: ' + error.message
      };
    }
  }

  /**
   * 验证目标值数据
   */
  validateTargetData(data) {
    const errors = [];
    const requiredFields = ['物料编码', '产品名称', '记录日期', '节拍', '标准人数', '生产线'];

    data.forEach((row, index) => {
      const rowNum = index + 2; // Excel行号从2开始

      // 检查必填字段
      requiredFields.forEach(field => {
        if (!row[field] || row[field].toString().trim() === '') {
          errors.push(`第${rowNum}行: ${field}不能为空`);
        }
      });

      // 验证数值字段
      if (row['节拍'] && (isNaN(row['节拍']) || row['节拍'] <= 0)) {
        errors.push(`第${rowNum}行: 节拍必须是大于0的数字`);
      }

      if (row['标准人数'] && (isNaN(row['标准人数']) || row['标准人数'] <= 0)) {
        errors.push(`第${rowNum}行: 标准人数必须是大于0的整数`);
      }

      // 验证可选的目标值字段（2位小数）
      if (row['整机目标值'] && row['整机目标值'].toString().trim() !== '') {
        const value = parseFloat(row['整机目标值']);
        if (isNaN(value) || value <= 0) {
          errors.push(`第${rowNum}行: 整机目标值必须是大于0的数字`);
        }
      }

      if (row['整机电机目标值'] && row['整机电机目标值'].toString().trim() !== '') {
        const value = parseFloat(row['整机电机目标值']);
        if (isNaN(value) || value <= 0) {
          errors.push(`第${rowNum}行: 整机电机目标值必须是大于0的数字`);
        }
      }
    });

    if (errors.length > 0) {
      return {
        success: false,
        message: `数据验证失败，共${errors.length}个错误`,
        errors: errors
      };
    }

    return { success: true };
  }

  /**
   * 插入目标值数据
   */
  async insertTargetData(data) {
    // 获取数据库连接
    let dbConnection = null;
    try {
      dbConnection = await this.getDbConnection();
      console.log('🔗 目标值导入: 获取数据库连接成功');
    } catch (error) {
      console.error('❌ 目标值导入: 获取数据库连接失败:', error.message);
      return {
        success: false,
        message: `获取数据库连接失败: ${error.message}`,
        successCount: 0,
        errorCount: data.length,
        errors: [`获取数据库连接失败: ${error.message}`]
      };
    }

    return new Promise((resolve) => {
      const stmt = dbConnection.prepare(`
        INSERT OR REPLACE INTO pph_targets
        (material_code, product_name, product_series, record_date,
         takt_time, whole_machine_target, motor_target, standard_staff, line_name, is_active)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 1)
      `);

      let successCount = 0;
      let errorCount = 0;
      let totalRecords = 0;

      // 预处理数据，计算总记录数（考虑生产线拆分）
      const processedData = [];
      const errors = [];
      // 影响范围收集
      const impacted = { line_names: new Set(), material_codes: new Set(), from_date: null, to_date: null };

      data.forEach((row, index) => {
        const lines = this.splitProductionLines(row['生产线']);
        lines.forEach(lineName => {
          processedData.push({
            ...row,
            lineName: lineName,
            originalIndex: index
          });
        });
      });

      totalRecords = processedData.length;

      processedData.forEach((processedRow) => {
        try {
          // 标准化日期格式
          const recordDate = this.standardizeDate(processedRow['记录日期']);

          // 收集影响范围
          try {
            impacted.line_names.add(String(processedRow.lineName).trim());
            impacted.material_codes.add(String(processedRow['物料编码']).trim());
            if (recordDate) {
              if (!impacted.from_date || recordDate < impacted.from_date) impacted.from_date = recordDate;
              if (!impacted.to_date || recordDate > impacted.to_date) impacted.to_date = recordDate;
            }
          } catch(_) {}

          // 处理可选的目标值字段 - 清洗为2位小数
          let wholeMachineTarget = null;
          if (processedRow['整机目标值'] && processedRow['整机目标值'].toString().trim() !== '') {
            const value = parseFloat(processedRow['整机目标值']);
            if (!isNaN(value) && value > 0) {
              wholeMachineTarget = Math.round(value * 100) / 100; // 保留2位小数
            }
          }

          let motorTarget = null;
          if (processedRow['整机电机目标值'] && processedRow['整机电机目标值'].toString().trim() !== '') {
            const value = parseFloat(processedRow['整机电机目标值']);
            if (!isNaN(value) && value > 0) {
              motorTarget = Math.round(value * 100) / 100; // 保留2位小数
            }
          }

          // 清洗节拍为2位小数
          const taktTime = Math.round(parseFloat(processedRow['节拍']) * 100) / 100;

          // 使用 INSERT OR REPLACE 基于唯一键 (material_code, record_date, line_name) 更新
          stmt.run([
            processedRow['物料编码'].toString().trim(),
            processedRow['产品名称'].toString().trim(),
            processedRow['产品系列'] ? processedRow['产品系列'].toString().trim() : null,
            recordDate,
            taktTime,
            wholeMachineTarget,
            motorTarget,
            parseInt(processedRow['标准人数']),
            processedRow.lineName
          ], function(err) {
            if (err) {
              errorCount++;
              errors.push(`第${processedRow.originalIndex + 2}行(${processedRow.lineName}): ${err.message}`);
            } else {
              successCount++;
            }

            // 检查是否完成
            if (successCount + errorCount === totalRecords) {
              stmt.finalize();
              resolve({
                success: true,
                message: `导入完成: 成功${successCount}条，失败${errorCount}条`,
                successCount,
                errorCount,
                totalRecords,
                errors: errors.length > 0 ? errors : undefined,
                impacted: {
                  line_names: Array.from(impacted.line_names),
                  material_codes: Array.from(impacted.material_codes),
                  from_date: impacted.from_date,
                  to_date: impacted.to_date
                }
              });
            }
          });
        } catch (error) {
          errorCount++;
          errors.push(`第${processedRow.originalIndex + 2}行(${processedRow.lineName}): ${error.message}`);

          if (successCount + errorCount === totalRecords) {
            stmt.finalize();
            resolve({
              success: false,
              message: `导入失败: 成功${successCount}条，失败${errorCount}条`,
              successCount,
              errorCount,
              totalRecords,
              errors: errors,
              impacted: {
                line_names: Array.from(impacted.line_names),
                material_codes: Array.from(impacted.material_codes),
                from_date: impacted.from_date,
                to_date: impacted.to_date
              }
            });
          }
        }
      });
    });
  }

  /**
   * 拆分生产线字符串
   */
  /**
   * 拆分生产线字段
   * 支持多种分隔符和格式：
   * 1. 中文顿号：1、2、3
   * 2. 英文逗号：1,2,3 或 1, 2, 3
   * 3. 中文逗号：1，2，3 或 1， 2， 3
   * 4. 分号：1;2;3 或 1; 2; 3
   * 5. 点号+空格：3. 5 或 3. 4. 5（表示生产线3、5或3、4、5）
   * 6. 换行符分隔
   * 7. 空格分隔：1 2 3
   */
  splitProductionLines(lineString) {
    if (!lineString) return [''];

    const lineStr = lineString.toString().trim();
    if (!lineStr) return [''];

    let lines = [];

    // 定义所有可能的分隔符（按优先级排序）
    const separators = [
      { pattern: /、/g, name: '中文顿号' },           // 中文顿号
      { pattern: /，/g, name: '中文逗号' },           // 中文逗号
      { pattern: /,/g, name: '英文逗号' },            // 英文逗号
      { pattern: /；/g, name: '中文分号' },           // 中文分号
      { pattern: /;/g, name: '英文分号' },            // 英文分号
      { pattern: /\n|\r\n|\r/g, name: '换行符' },     // 换行符
    ];

    // 检查常规分隔符
    for (const sep of separators) {
      if (sep.pattern.test(lineStr)) {
        lines = lineStr.split(sep.pattern)
          .map(line => line.trim())
          .filter(line => line.length > 0);
        break;
      }
    }

    // 如果没有找到常规分隔符，检查特殊格式
    if (lines.length === 0) {
      // 检查点号+空格格式（如 "3. 5" 或 "3. 4. 5"）
      if (lineStr.includes('. ')) {
        const parts = lineStr.split(/\.\s*/);
        lines = parts
          .map(part => part.trim())
          .filter(part => part.length > 0 && !isNaN(part));
      }
      // 检查纯空格分隔（如 "1 2 3"，但要排除单个数字+空格的情况）
      else if (/\s+/.test(lineStr) && lineStr.split(/\s+/).length > 1) {
        const parts = lineStr.split(/\s+/);
        // 只有当所有部分都是数字或简短字符串时才认为是空格分隔
        if (parts.every(part => part.length <= 3)) {
          lines = parts.filter(part => part.length > 0);
        }
      }
    }

    // 如果仍然没有找到分隔符，返回原字符串
    if (lines.length === 0) {
      lines = [lineStr];
    }

    return lines;
  }

  /**
   * 标准化日期格式
   */
  standardizeDate(dateInput) {
    try {
      if (!dateInput) return null;

      let dateStr = dateInput.toString().trim();
      if (!dateStr) return null;

      // 1) Excel 序列号（一般为 5 位左右的纯数字，如 45512）
      if (/^\d{4,6}$/.test(dateStr)) {
        const n = parseInt(dateStr, 10);
        // 合理范围内再转换，避免把“2024”误判为序列号
        if (n > 20000 && n < 80000) {
          const excelDate = new Date((n - 25569) * 86400 * 1000);
          if (!isNaN(excelDate.getTime())) {
            return excelDate.toISOString().slice(0, 10);
          }
        }
      }

      // 2) 统一分隔符
      const s = dateStr.replace(/[\/\.]/g, '-').replace(/\s+/g, '-');
      const parts = s.split('-').filter(Boolean);

      // 尝试解析常见的三段式日期
      if (parts.length === 3) {
        let [a, b, c] = parts.map((p) => parseInt(p, 10));
        let year, month, day;

        if (parts[0].length === 4) {
          // YYYY-M-D
          year = a; month = b; day = c;
        } else if (parts[2].length === 4) {
          // M-D-YYYY 或 D-M-YYYY：优先按 M-D-YYYY 解析
          if (a <= 12 && b <= 31) { month = a; day = b; year = c; }
          else { day = a; month = b; year = c; }
        } else {
          // 全是两位数：优先按 M-D-YY 解析（与本项目 Excel 模板一致）
          if (a <= 12 && b <= 31) { month = a; day = b; year = c; }
          else if (a > 31 && b <= 12 && c <= 31) { year = a; month = b; day = c; }
          else { year = a; month = b; day = c; }
          if (year < 100) year += 2000; // 24 -> 2024
        }

        if (year < 100) year += 2000;
        const date = new Date(year, (month || 0) - 1, day || 0);
        if (!isNaN(date.getTime()) && date.getFullYear() === year && date.getMonth() === (month - 1) && date.getDate() === day) {
          const y = String(year).padStart(4, '0');
          const m = String(month).padStart(2, '0');
          const d = String(day).padStart(2, '0');
          return `${y}-${m}-${d}`;
        }
      }

      // 3) 兜底：让 JS Date 解析（尽量统一到 UTC ISO）
      const dt = new Date(s);
      if (!isNaN(dt.getTime())) {
        return dt.toISOString().slice(0, 10);
      }

      console.warn('⚠️ 无法标准化日期，返回原值:', dateInput);
      return dateStr; // 保底返回原始字符串，避免中断导入
    } catch (e) {
      console.error('standardizeDate 解析异常:', e && e.message);
      return null;
    }

    // 时间标准化使用模块级标准函数 standardizeTimeHelper()
  }


  /**
   * 导入PPH预估值
   */
  async importPPHEstimates(filePath) {
    // 获取数据库连接
    let dbConnection = null;
    try {
      dbConnection = await this.getDbConnection();
      console.log('🔗 预估值导入: 获取数据库连接成功');
    } catch (error) {
      console.error('❌ 预估值导入: 获取数据库连接失败:', error.message);
      return {
        success: false,
        message: `获取数据库连接失败: ${error.message}`
      };
    }

    try {
      // 读取Excel文件
      const workbook = XLSX.readFile(filePath);
      const sheetName = workbook.SheetNames[0];
      const worksheet = workbook.Sheets[sheetName];
      const jsonData = XLSX.utils.sheet_to_json(worksheet, { defval: '', blankrows: false, raw: false, rawNumbers: true });

      if (jsonData.length === 0) {
        return { success: false, message: 'Excel文件为空' };
      }

      // 验证数据格式 - 检查必要字段是否存在
      const firstRow = jsonData[0];
      console.log('📋 预估值导入: 检测到的字段名:', Object.keys(firstRow));

      // 检查必要字段（支持多种字段名）
      const hasRequiredFields = (
        (firstRow['物料编码'] !== undefined || firstRow['料号'] !== undefined) &&
        (firstRow['生产线名称'] !== undefined || firstRow['生产线'] !== undefined) &&
        (firstRow['预估PPH值'] !== undefined || firstRow['预估值'] !== undefined)
      );

      if (!hasRequiredFields) {
        return {
          success: false,
          message: `Excel文件缺少必要字段。需要包含: 物料编码(或料号)、生产线名称(或生产线)、预估PPH值(或预估值)。建议使用系统提供的模板。`
        };
      }

      // 检查产品名称字段（可选但推荐）
      const hasProductName = (firstRow['产品名称'] !== undefined || firstRow['产品名'] !== undefined);
      if (!hasProductName) {
        console.log('⚠️ 预估值导入: 未检测到产品名称字段，将使用物料编码作为产品名称');
      }

      let successCount = 0;
      let errorCount = 0;
      const errors = [];

      // 处理数据
      for (let i = 0; i < jsonData.length; i++) {
        const row = jsonData[i];
        const rowNum = i + 2; // Excel行号从2开始

        try {
          // 字段映射和验证 - 支持多种字段名
          const material_code = row['物料编码'] || row['料号'];
          const product_name = row['产品名称'] || row['产品名'];
          const line_name = row['生产线'] || row['生产线名称'];
          const estimate_pph_raw = row['预估PPH值'] || row['预估值'];

          // 验证必填字段
          if (!material_code || material_code.toString().trim() === '') {
            errors.push(`第${rowNum}行：物料编码不能为空`);
            errorCount++;
            continue;
          }

          if (!line_name || line_name.toString().trim() === '') {
            errors.push(`第${rowNum}行：生产线名称不能为空`);
            errorCount++;
            continue;
          }

          // 清洗预估PPH值为2位小数
          let estimate_pph = null;
          if (estimate_pph_raw !== undefined && estimate_pph_raw !== null && estimate_pph_raw.toString().trim() !== '') {
            const value = parseFloat(estimate_pph_raw);
            if (isNaN(value) || value <= 0) {
              errors.push(`第${rowNum}行：预估PPH值必须是大于0的数字`);
              errorCount++;
              continue;
            }
            estimate_pph = Math.round(value * 100) / 100; // 保留2位小数
          } else {
            errors.push(`第${rowNum}行：预估PPH值不能为空`);
            errorCount++;
            continue;
          }

          // 拆分生产线（与目标值导入逻辑一致）
          const lines = this.splitProductionLines(line_name.toString().trim());

          // 为每个生产线创建预估值记录
          for (const lineName of lines) {
            await this.insertEstimateRecord({
              material_code: material_code.toString().trim(),
              product_name: product_name ? product_name.toString().trim() : material_code.toString().trim(), // 如果产品名称为空，使用物料编码
              line_name: lineName,
              estimate_pph: estimate_pph
            }, dbConnection);

            successCount++;
          }

        } catch (error) {
          errorCount++;
          errors.push(`第${rowNum}行: ${error.message}`);
          console.error(`第${rowNum}行处理失败:`, error.message);
        }
      }

      // 清理临时文件
      if (fs.existsSync(filePath)) {
        fs.unlinkSync(filePath);
      }

      console.log(`✅ 预估值导入完成: 成功${successCount}条，失败${errorCount}条`);

      // 汇总影响范围
      const impacted = { line_names: new Set(), material_codes: new Set() };
      for (const row of jsonData) {
        const material_code = row['物料编码'] || row['料号'];
        const line_name = row['生产线'] || row['生产线名称'];
        if (material_code) impacted.material_codes.add(String(material_code).trim());
        if (line_name) {
          const lines = this.splitProductionLines(String(line_name).trim());
          lines.forEach(l=>impacted.line_names.add(l));
        }
      }

      return {
        success: errorCount === 0,
        message: `导入完成: 成功${successCount}条，失败${errorCount}条`,
        successCount,
        errorCount,
        totalRecords: jsonData.length,
        errors: errors.length > 0 ? errors.slice(0, 20) : undefined,
        impacted: {
          line_names: Array.from(impacted.line_names),
          material_codes: Array.from(impacted.material_codes)
        }
      };

    } catch (error) {
      console.error('❌ 预估值导入失败:', error);

      // 清理临时文件
      if (fs.existsSync(filePath)) {
        fs.unlinkSync(filePath);
      }

      return {
        success: false,
        message: '导入失败: ' + error.message
      };
    } finally {
      // 释放数据库连接
      if (dbConnection) {
        await this.releaseDbConnection(dbConnection);
      }
    }
  }

  /**
   * 插入预估值记录到数据库
   */
  async insertEstimateRecord(data, dbConnection) {
    return new Promise((resolve, reject) => {
      const sql = `INSERT OR REPLACE INTO pph_estimates (material_code, product_name, line_name, estimate_pph, created_at, updated_at)
                   VALUES (?, ?, ?, ?, datetime('now'), datetime('now'))`;

      const values = [
        data.material_code,
        data.product_name || '', // 确保product_name不为空
        data.line_name,
        data.estimate_pph
      ];

      dbConnection.run(sql, values, function(err) {
        if (err) {
          reject(new Error(`预估值数据库插入失败: ${err.message}`));
        } else {
          resolve(this.lastID);
        }
      });
    });
  }

  /**
   * 生成PPH目标值导入模板
   */
  generateTargetTemplate() {
    const workbook = XLSX.utils.book_new();
    const templateData = [
      {
        '物料编码': 'A01-003630',
        '产品名称': 'AW亚马逊 FE7-50G3dOL(WWH)（户外白）',
        '产品系列': 'FE7-G3',
        '记录日期': '2024/6/5',
        '节拍': 50.00,
        '整机目标值': 3.27,
        '整机电机目标值': '',
        '标准人数': 22,
        '生产线': '3、5'
      },
      {
        '物料编码': 'P001',
        '产品名称': '产品A型号1',
        '产品系列': 'A系列',
        '记录日期': '2025-1-1',
        '节拍': 45.0,
        '整机目标值': 25.0,
        '整机电机目标值': 30.0,
        '标准人数': 8,
        '生产线': '生产线A'
      },
      {
        '物料编码': 'P002',
        '产品名称': '产品B型号1',
        '产品系列': '',
        '记录日期': '2025/01/01',
        '节拍': 55.0,
        '整机目标值': 20.0,
        '整机电机目标值': '',
        '标准人数': 10,
        '生产线': '生产线A,生产线B'
      }
    ];

    const worksheet = XLSX.utils.json_to_sheet(templateData);

    // 设置列宽
    const colWidths = [
      { wch: 15 }, // 物料编码
      { wch: 35 }, // 产品名称
      { wch: 15 }, // 产品系列
      { wch: 12 }, // 记录日期
      { wch: 10 }, // 节拍
      { wch: 12 }, // 整机目标值
      { wch: 15 }, // 整机电机目标值
      { wch: 10 }, // 标准人数
      { wch: 20 }  // 生产线
    ];
    worksheet['!cols'] = colWidths;

    XLSX.utils.book_append_sheet(workbook, worksheet, 'PPH目标值');

    return workbook;
  }

  /**
   * 生成PPH预估值导入模板
   */
  generateEstimateTemplate() {
    const workbook = XLSX.utils.book_new();
    const templateData = [
      {
        '物料编码': 'P001',
        '产品名称': '产品A',
        '生产线名称': '生产线A',
        '预估PPH值': 25.5
      },
      {
        '物料编码': 'P002',
        '产品名称': '产品B',
        '生产线名称': '生产线B',
        '预估PPH值': 30.0
      }
    ];

    const worksheet = XLSX.utils.json_to_sheet(templateData);

    // 设置列宽
    const colWidths = [
      { wch: 15 }, // 物料编码
      { wch: 25 }, // 产品名称
      { wch: 15 }, // 生产线名称
      { wch: 15 }  // 预估PPH值
    ];
    worksheet['!cols'] = colWidths;

    XLSX.utils.book_append_sheet(workbook, worksheet, 'PPH预估值');

    return workbook;
  }

  /**
   * 生成PPH记录导入模板
   */
  generateRecordsTemplate() {
    const workbook = XLSX.utils.book_new();
    const templateData = [
      {
        '记录日期': '2025-01-01',
        '生产线': '生产线A',
        '订单号': 'SCDD20250101001',
        '开始时间': '08:00',
        '结束时间': '17:00',
        '完成数量': 100,
        '总人数': 12,
        '线上人员': 10,
        '线外人员': 2,
        '是否符合标准作业': '是',
        '皮带拉速度（秒/格）': '',
        '异常分类': '',
        '异常描述': '',
        '异常损失（分钟）': 0,
        '转产时间': 0,
        '小休时间': 10,
        '物料编码': 'P001',
        '产品名称': '产品A',
        '需求分类': '',
        '生产类型': '整机生产'
      }
    ];

    const worksheet = XLSX.utils.json_to_sheet(templateData);

    // 设置列宽
    const colWidths = [
      { wch: 12 }, // 记录日期
      { wch: 12 }, // 生产线
      { wch: 18 }, // 订单号
      { wch: 10 }, // 开始时间
      { wch: 10 }, // 结束时间
      { wch: 10 }, // 完成数量
      { wch: 10 }, // 总人数
      { wch: 10 }, // 线上人员
      { wch: 10 }, // 线外人员
      { wch: 15 }, // 是否符合标准作业
      { wch: 15 }, // 皮带拉速度
      { wch: 12 }, // 异常分类
      { wch: 20 }, // 异常描述
      { wch: 15 }, // 异常损失
      { wch: 12 }, // 转产时间
      { wch: 12 }, // 小休时间
      { wch: 12 }, // 维护时间
      { wch: 12 }, // 主打产品
      { wch: 15 }, // 是否整机生产
      { wch: 15 }, // 物料编码
      { wch: 20 }, // 产品名称
      { wch: 12 }, // 需求分类
      { wch: 15 }  // 预估PPH
    ];

    worksheet['!cols'] = colWidths;
    XLSX.utils.book_append_sheet(workbook, worksheet, 'PPH记录');

    return workbook;
  }

  /**
   * 导入PPH记录（简化版本，基于备份文件）
   */
  async importPPHRecords(filePath) {
    try {
      console.log('🔍 开始导入PPH记录:', filePath);

      // 读取Excel文件
      const workbook = XLSX.readFile(filePath);
      const sheetNames = workbook.SheetNames || [];
      const preferred = ['PPH记录', 'PPH记录历史', '记录', 'Sheet1'];
      const sheetName = preferred.find(n => sheetNames.includes(n)) || sheetNames[0];
      const worksheet = workbook.Sheets[sheetName];
      const ref = worksheet && worksheet['!ref'];
      const rows = XLSX.utils.sheet_to_json(worksheet, { defval: '', blankrows: false, raw: false, rawNumbers: true });

      console.log('📄 工作表选择:', { sheetNames, sheetName, ref });
      console.log(`📊 读取到 ${rows.length} 行记录数据`);

      if (!Array.isArray(rows) || rows.length === 0) {
        return { success: false, message: 'Excel文件为空' };
      }

      let successCount = 0;
      let errorCount = 0;
      const errors = [];

      // 获取数据库连接
      const dbConnection = await this.getDbConnection();

      try {
        // 逐行处理
        for (let i = 0; i < rows.length; i++) {
          const row = rows[i];
          const rowNum = i + 2; // Excel行号从2开始

          try {
            // 基本字段验证 - 严格中文列名（不做容错）
            const requiredFieldsMap = {
              '记录日期': row['记录日期'],
              '生产线': row['生产线'],
              '物料编码': row['物料编码'],
              '产品名称': row['产品名称'],
              '生产类型': row['生产类型']
            };

            const missingFields = [];
            for (const [fieldName, value] of Object.entries(requiredFieldsMap)) {
              if (!value || String(value).trim() === '') {
                missingFields.push(fieldName);
              }
            }

            if (missingFields.length > 0) {
              errorCount++;
              errors.push(`第${rowNum}行: 缺少必填字段 ${missingFields.join(', ')}`);
              continue;
            }

            // 数据处理和PPH计算（使用统一字段标准）
            const quantity = row['完成数量'] ? Number(row['完成数量']) : 0;
            const working = row['线上人员'] !== undefined && row['线上人员'] !== null ? Number(row['线上人员']) : 0;
            const offline = row['线外人员'] !== undefined && row['线外人员'] !== null ? Number(row['线外人员']) : 0;
            const totalPeople = Math.max(1, working + offline);
            const startTime = row['开始时间'] ? standardizeTimeHelper(String(row['开始时间']).trim()) : '08:00';
            const endTime = row['结束时间'] ? standardizeTimeHelper(String(row['结束时间']).trim()) : '17:00';

            // 计算工作时间（分钟）
            const totalMinutes = this.calculateWorkingMinutes(startTime, endTime);

            // 计算损失时间（简化版本）
            const abnormalMinutes = row['异常时间'] ? Number(row['异常时间']) : 0;
            const breakMinutes = row['休息时间'] ? Number(row['休息时间']) : 0;
            const changeoverMinutes = row['换线时间'] ? Number(row['换线时间']) : 0;
            const maintenanceMinutes = row['维护时间'] ? Number(row['维护时间']) : 0;

            const lossMinutes = abnormalMinutes + breakMinutes + changeoverMinutes + maintenanceMinutes;
            const netMinutes = Math.max(0, totalMinutes - lossMinutes);
            const netHours = netMinutes / 60;
            const people = Math.max(1, totalPeople);

            // 计算PPH（参考备份文件公式）
            const calculatedPPH = (netHours > 0 && people > 0) ? quantity / (netHours * people) : 0;
            const actualTakt = calculatedPPH > 0 ? Math.round((3600 / calculatedPPH) * 100) / 100 : 0;

            // 生产类型 → is_whole_machine（1=整机生产, 0=整机+电机生产）
            const typeInput = (row['生产类型'] || '').toString().trim();
            const isWholeMachine = (typeInput === '整机生产' || typeInput === '整机');

            // 异常字段提取与标准化
            const abnormalCategory = (row['异常分类'] ?? row['异常类别'] ?? '').toString().trim();
            const abnormalDescription = (row['异常描述'] ?? '').toString().trim();
            const abnormalClosedRaw = (row['异常关闭'] ?? row['是否关闭'] ?? '').toString().trim();
            const abnormalClosed = (() => {
              if (abnormalClosedRaw === '') return null;
              const v = abnormalClosedRaw.toLowerCase();
              if (v === '是' || v === 'y' || v === 'yes' || v === '1' || v === 'true') return 1;
              if (v === '否' || v === 'n' || v === 'no' || v === '0' || v === 'false') return 0;
              // 非法值一律视为 NULL，避免脏数据
              return null;
            })();

            const recordData = {
              record_date: this.standardizeDate(requiredFieldsMap['记录日期']),
              line_name: String(requiredFieldsMap['生产线']).trim(),
              material_code: String(requiredFieldsMap['物料编码']).trim(),
              product_name: String(requiredFieldsMap['产品名称']).trim(),
              order_number: row['订单号'] ? String(row['订单号']).trim() : `IMPORT_${Date.now()}_${i}`,
              start_time: startTime,
              end_time: endTime,
              quantity: quantity,  // 使用统一字段名
              working_people: working,  // 使用统一字段名
              offline_staff: offline,
              total_people: totalPeople,  // 使用统一字段名
              calculated_pph: Math.round(calculatedPPH * 10) / 10,
              actual_takt_seconds: actualTakt,
              net_working_minutes: netMinutes,
              abnormal_loss_minutes: abnormalMinutes,
              break_minutes: breakMinutes,
              changeover_minutes: changeoverMinutes,
              is_whole_machine: isWholeMachine ? 1 : 0,
              // 异常字段
              abnormality_category: abnormalCategory || null,
              abnormality_description: abnormalDescription || null,
              abnormality_closed: abnormalClosed
            };

            console.log(`🔍 第${rowNum}行数据处理:`, {
              record_date: recordData.record_date,
              line_name: recordData.line_name,
              order_number: recordData.order_number,
              calculated_pph: recordData.calculated_pph,
              quantity: recordData.quantity,
              working_people: recordData.working_people,
              offline_staff: recordData.offline_staff,
              total_people: recordData.total_people
            });

            await this.insertPPHRecord(recordData, dbConnection);
            successCount++;

          } catch (error) {
            errorCount++;
            errors.push(`第${rowNum}行: ${error.message}`);
            console.error(`第${rowNum}行处理失败:`, error.message);
            if (!row['生产类型']) {
              console.error('提示: 记录导入模板需新增“生产类型”，值为“整机生产/整机+电机生产”');
            }
          }
        }

        console.log(`✅ 记录导入完成: 成功${successCount}条，失败${errorCount}条`);

        // 汇总影响范围（基于导入记录的日期/线体/物料）
        const impacted = { line_names: new Set(), material_codes: new Set(), from_date: null, to_date: null }
        for (const row of rows) {
          const date = this.standardizeDate(row['记录日期']);
          const line = row['生产线'] ? String(row['生产线']).trim() : '';
          const mat = row['物料编码'] ? String(row['物料编码']).trim() : '';
          if (line) impacted.line_names.add(line)
          if (mat) impacted.material_codes.add(mat)
          if (date) {
            if (!impacted.from_date || date < impacted.from_date) impacted.from_date = date
            if (!impacted.to_date || date > impacted.to_date) impacted.to_date = date
          }
        }

        return {
          success: errorCount === 0,
          message: `导入完成: 成功${successCount}条，失败${errorCount}条`,
          totalRecords: rows.length,
          successCount,
          errorCount,
          errors: errors.slice(0, 20),
          impacted: {
            line_names: Array.from(impacted.line_names),
            material_codes: Array.from(impacted.material_codes),
            from_date: impacted.from_date,
            to_date: impacted.to_date
          }
        };

      } finally {
        // 释放数据库连接
        await this.releaseDbConnection(dbConnection);

        // 删除临时文件
        if (fs.existsSync(filePath)) {
          fs.unlinkSync(filePath);
        }
      }

    } catch (error) {
      console.error('❌ 记录导入失败:', error);

      // 删除临时文件
      if (fs.existsSync(filePath)) {
        fs.unlinkSync(filePath);
      }

      return {
        success: false,
        message: '导入失败: ' + error.message,
        totalRecords: 0,
        successCount: 0,
        errorCount: 0,
        errors: [error.message]
      };
    }
  }

  /**
   * 计算工作时间（分钟）
   */
  calculateWorkingMinutes(startTime, endTime) {
    try {
      // 简化时间计算，假设同一天内
      const start = startTime.split(':');
      const end = endTime.split(':');

      const startMinutes = parseInt(start[0]) * 60 + parseInt(start[1] || 0);
      const endMinutes = parseInt(end[0]) * 60 + parseInt(end[1] || 0);

      let totalMinutes = endMinutes - startMinutes;

      // 如果结束时间小于开始时间，假设跨天
      if (totalMinutes < 0) {
        totalMinutes += 24 * 60;
      }

      return Math.max(0, totalMinutes);
    } catch (error) {
      console.warn('时间计算错误:', error.message);
      return 480; // 默认8小时
    }
  }

  /**
   * 插入PPH记录到数据库 - 统一新命名
   */
  async insertPPHRecord(data, dbConnection) {
    return new Promise((resolve, reject) => {
      // 动态读取表结构，按是否存在 is_whole_machine 构造 SQL，避免列/占位符不一致
      dbConnection.all("PRAGMA table_info(pph_records)", [], async (pragmaErr, pragmaRows) => {
        if (pragmaErr) {
          console.error('读取表结构失败:', pragmaErr.message);
          reject(new Error(`读取表结构失败: ${pragmaErr.message}`));
          return;
        }
        const colSet = new Set((pragmaRows || []).map(r => r.name));
        const includeIsWhole = colSet.has('is_whole_machine');

        const baseCols = [
          'record_date','line_name','order_number','material_code','product_name',
          'start_time','end_time',
          'quantity','working_people','offline_staff','total_people',
          'break_minutes','changeover_minutes',
          'abnormal_loss_minutes','net_working_minutes',
          'calculated_pph','actual_takt_seconds',
          // 异常相关列（如表结构存在则写入，若不存在也不影响占位匹配，因为我们按列集合动态生成）
          'abnormality_category','abnormality_description','abnormality_closed'
        ];
        const cols = includeIsWhole ? [...baseCols, 'is_whole_machine'] : baseCols;

        // 内联计算目标PPH/预估PPH/达成率（轻量同步查询）
        // 仅在 material_code/line_name/record_date 可用时执行
        const canCalc = !!(data.material_code && data.line_name && data.record_date);
        let target_pph = null, estimate_pph = null, efficiency_rate = null;
        if (canCalc) {
          try {
            const pPrev = [data.material_code, data.line_name, data.record_date];
            const qPrev = `SELECT record_date, whole_machine_target, motor_target FROM pph_targets
                           WHERE UPPER(material_code)=UPPER(?) AND TRIM(line_name)=TRIM(?) AND record_date <= ? AND (is_active = 1 OR is_active IS NULL)
                           ORDER BY record_date DESC LIMIT 1`;
            const qNext = `SELECT record_date, whole_machine_target, motor_target FROM pph_targets
                           WHERE UPPER(material_code)=UPPER(?) AND TRIM(line_name)=TRIM(?) AND record_date >= ? AND (is_active = 1 OR is_active IS NULL)
                           ORDER BY record_date ASC LIMIT 1`;
            const prev = await new Promise((res)=>(dbConnection.all(qPrev, pPrev, (e,r)=>res(r&&r[0]))));
            let use = prev;
            if (!use) {
              use = await new Promise((res)=>(dbConnection.all(qNext, pPrev, (e,r)=>res(r&&r[0]))));
            }
            if (use) {
              const isWhole = data.is_whole_machine === 1 || data.is_whole_machine === true || data.is_whole_machine === '1';
              target_pph = isWhole ? (use.whole_machine_target ?? null) : (use.motor_target ?? null);
            }
            const estSql = "SELECT estimate_pph FROM pph_estimates WHERE UPPER(material_code)=UPPER(?) AND TRIM(line_name)=TRIM(?) ORDER BY updated_at DESC LIMIT 1";
            const estRow = await new Promise((res)=>(dbConnection.all(estSql, [data.material_code, data.line_name], (e,r)=>res(r&&r[0]))));
            estimate_pph = estRow ? estRow.estimate_pph : null;
            const baseline = (target_pph!=null && target_pph>0) ? target_pph : ((estimate_pph!=null && estimate_pph>0)?estimate_pph:null);
            if (baseline && baseline>0) {
              const calc = Number(data.calculated_pph) || 0;
              efficiency_rate = calc>0 ? Math.round(((calc/baseline)*100)*100)/100 : null;
            }
          } catch (e) {
            console.warn('内联计算目标/预估失败(忽略):', e && e.message);
          }
        }

        // 如果表存在这些列，则在 UPSERT 时写入对应值
        const includeTarget = colSet.has('target_pph');
        const includeEstimate = colSet.has('estimate_pph');
        const includeEff = colSet.has('efficiency_rate');
        const includeAbnClosed = colSet.has('abnormality_closed');

        // 由于 baseCols 已包含异常列，这里只需要追加目标/预估/达成率等可选列
        const extraCols = [
          ...(includeTarget ? ['target_pph'] : []),
          ...(includeEstimate ? ['estimate_pph'] : []),
          ...(includeEff ? ['efficiency_rate'] : [])
        ];
        const allCols = [...cols, ...extraCols];

        const placeholders = allCols.map(() => '?').join(', ');
        const sql = `INSERT INTO pph_records (${allCols.join(', ')}, created_at, updated_at) VALUES (${placeholders}, datetime('now'), datetime('now'))
          ON CONFLICT(record_date, line_name, material_code, start_time, end_time)
          DO UPDATE SET
            quantity=excluded.quantity,
            working_people=excluded.working_people,
            offline_staff=excluded.offline_staff,
            total_people=excluded.total_people,
            break_minutes=excluded.break_minutes,
            changeover_minutes=excluded.changeover_minutes,
            abnormal_loss_minutes=excluded.abnormal_loss_minutes,
            net_working_minutes=excluded.net_working_minutes,
            calculated_pph=excluded.calculated_pph,
            actual_takt_seconds=excluded.actual_takt_seconds,
            ${includeTarget ? 'target_pph=excluded.target_pph,' : ''}
            ${includeEstimate ? 'estimate_pph=excluded.estimate_pph,' : ''}
            ${includeEff ? 'efficiency_rate=excluded.efficiency_rate,' : ''}
            abnormality_category=excluded.abnormality_category,
            abnormality_description=excluded.abnormality_description,
            ${includeAbnClosed ? 'abnormality_closed=excluded.abnormality_closed,' : ''}
            updated_at=CURRENT_TIMESTAMP`;

        const values = [
          data.record_date,
          data.line_name,
          data.order_number,
          data.material_code || '',
          data.product_name || '',
          data.start_time,
          data.end_time,
          Number(data.quantity) || 0,
          Number(data.working_people) || 0,
          Number(data.offline_staff) || 0,
          Number(data.total_people) || 1,
          Number(data.break_minutes) || 0,
          Number(data.changeover_minutes) || 0,
          Number(data.abnormal_loss_minutes) || 0,
          Number(data.net_working_minutes) || 0,
          Number(data.calculated_pph) || 0,
          Number(data.actual_takt_seconds) || 0,
          // 异常字段值
          data.abnormality_category ?? null,
          data.abnormality_description ?? null,
          (includeAbnClosed ? (data.abnormality_closed ?? null) : undefined),
          ...(includeIsWhole ? [((data.is_whole_machine === 1 || data.is_whole_machine === 0) ? data.is_whole_machine : null)] : []),
          ...(includeTarget ? [target_pph] : []),
          ...(includeEstimate ? [estimate_pph] : []),
          ...(includeEff ? [efficiency_rate] : [])
        ].filter(v => v !== undefined);

        console.log('🔍 插入/更新PPH记录数据(UPSERT):', {
          record_date: data.record_date,
          line_name: data.line_name,
          order_number: data.order_number,
          material_code: data.material_code,
          product_name: data.product_name,
          quantity: data.quantity,
          working_people: data.working_people,
          offline_staff: data.offline_staff,
          total_people: data.total_people,
          net_working_minutes: data.net_working_minutes,
          calculated_pph: data.calculated_pph,
          actual_takt_seconds: data.actual_takt_seconds,
          ...(includeIsWhole ? { is_whole_machine: data.is_whole_machine } : {}),
          ...(includeTarget ? { target_pph } : {}),
          ...(includeEstimate ? { estimate_pph } : {}),
          ...(includeEff ? { efficiency_rate } : {})
        });

        dbConnection.run(sql, values, function(err) {
          if (err) {
            console.error('❌ PPH记录UPSERT失败:', err.message);
            console.error('📊 UPSERT数据:', values);
            reject(new Error(`记录数据库UPSERT失败: ${err.message}`));
          } else {
            console.log('✅ PPH记录UPSERT成功, rowid:', this.lastID);
            resolve(this.lastID);
          }
        });
      });
    });
  }
}

module.exports = ExcelImportService;
